#!/usr/bin/env python
# Create: 2018/12/20
__author__ = '749B'
"""根据CPSL编号，查询线路的流量
先去查id，在通过id去traffic表里查出流量
"""
import requests

from sds.swis_login import swis


def get_info_by_cpsl(cpsl):
    results = swis.query("SELECT TOP 30 Alias, InterfaceID, Status, StatusDescription "
                         "FROM Orion.NPM.Interfaces "
                         "WHERE Alias LIKE @cpsl", cpsl='%' + cpsl + '%')
    res = results.get('results')
    if not res:
        print("未查询到记录,cpsl: %s" % cpsl)
    return res


def get_ids(objs):
    return [i.get('InterfaceID') for i in objs if 'InterfaceID' in i]


def traffic_by_id(id):
    sql_str = "SELECT InterfaceID, Min(DateTime) AS S_Time , Max(DateTime) AS E_Time, " \
              "Avg(InAveragebps) AS InAvg, " \
              "Max(InMaxbps) AS InMax, " \
              "Min(InMinbps) AS InMin, " \
              "Avg(OutAveragebps) AS OutAvg, " \
              "Max(OutMaxbps) AS OutMax, " \
              "Min(OutMinbps) AS OutMin " \
              "FROM Orion.NPM.InterfaceTraffic " \
              "WHERE InterfaceID in @id " \
              "AND DateTime >= '2018-12-18 06:00:00' AND DateTime <= '2018-12-18 08:00:00' " \
              "GROUP BY InterfaceID"
    # 上面的是UTC时间，对应的就是 14:00-16:00
    results = swis.query(sql_str, id=id)
    res = results.pop('results')
    if results:
        print(id, "Traffic 查询返回其他内容", results)
    if len(res) == 0:
        return {}
    assert len(res) == 1, "traffic_by_id 返回多个结果"
    return res[0]


def traffics(id):
    sql_str = "SELECT TOP 100 InterfaceID, DateTime, " \
              "InAveragebps, OutAveragebps " \
              "FROM Orion.NPM.InterfaceTraffic " \
              "WHERE InterfaceID = @id " \
              "AND DateTime >= '2018-12-18 06:00' AND DateTime <= '2018-12-18 08:00' " \
              "ORDER BY DateTime"
    # 查询到的流量单位是bps，除1000000，就是Solarwinds页面上显示的Mbps的数值
    results = swis.query(sql_str, id=id)
    for item in results.get('results'):
        print(item)


def run(cpsl):
    res_dic = {}
    interface_res = get_info_by_cpsl(cpsl)
    if not interface_res:
        return res_dic

    ids = get_ids(interface_res)
    if len(ids) > 1:
        print("\n查找到多个id", ids)
        res_dic.update({'ids': ' '.join(map(lambda x: str(x), ids))})
        traffic_res = {}
        for item in interface_res:
            if item.get('StatusDescription') == 'Up':
                res_dic.update(item)
                traffic_res = traffic_by_id(item.get('InterfaceID'))
                break
    else:
        res_dic.update(interface_res[0])
        traffic_res = traffic_by_id(ids[0])

    # print(res_dic)
    # print(traffic_res)
    res_dic.update(traffic_res)
    return res_dic


def dic_to_line(cpsl, d, keys):
    l = list()
    l.append(cpsl)
    for k in keys:
        s = d.get(k)
        if s is not None:
            l.append(str(s))
    return ','.join(l)


requests.packages.urllib3.disable_warnings()
CSV_KEYS = ('StatusDescription', 'InAvg', 'InMax', 'InMin', 'OutAvg', 'OutMax', 'OutMin',
            'Alias', 'S_Time', 'E_Time', 'ids')

if __name__ == '__main__':
    res_f = open('results.csv', 'w', encoding='utf-8')
    res_f.writelines(('CPSL,', ','.join(CSV_KEYS), '\n'))
    with open('list.csv') as f:
        f.readline()
        n = 0
        last_cpsl = ''
        for line in f:
            n += 1
            cpsl = line.strip()
            if last_cpsl and cpsl != last_cpsl:
                continue
            else:
                last_cpsl = None
            print('\r处理第 %s 个, CPSL:' % n, cpsl, end='', flush=True)
            dic = run(cpsl)
            line = dic_to_line(cpsl, dic, CSV_KEYS)
            # print(line)
            res_f.writelines((line, '\n'))
            # if n > 10:
            #     break
    res_f.close()

